package jc.malpractice.service;

import cn.hutool.json.JSONArray;
import cn.hutool.json.JSONObject;
import jc.malpractice.db.DbUtil;

import java.sql.*;

public class AdminService {
    //根据事件上报种类查询所有
    public static JSONObject select(String event_code){
        Connection conn = DbUtil.getConn();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            if (conn == null && conn.isClosed()) {
                return null;
            }

            preparedStatement = conn.prepareStatement(
                    "SELECT * FROM event_all,event_kind,status_table where event_all.event_code = event_kind.kind_code and event_status = status_id and event_all.event_code = ? and event_all.event_status=2 " +
                            "");
            preparedStatement.setString(1,event_code);
            resultSet = preparedStatement.executeQuery();
            System.out.println(resultSet);

            // 获取列数
            ResultSetMetaData metaData = resultSet.getMetaData();
            int columnCount = metaData.getColumnCount();

            // 遍历ResultSet中的每条数据
            JSONArray array = new JSONArray();
            while (resultSet.next()) {
                JSONObject jsonObj = new JSONObject();

                // 遍历每一列
                for (int i = 1; i <= columnCount; i++) {

                    String columnName = metaData.getColumnLabel(i);
                    Object value = resultSet.getObject(columnName);
                    jsonObj.set(columnName,value);
                }

                //查询粗略信息
                Connection con = DbUtil.getConn();
                PreparedStatement pstmt = con.prepareStatement("select property_en_name,detailed_data from event_property,event_detailed where event_property.property_code = event_detailed.property_code and form_code = ?");
                pstmt.setString(1,jsonObj.getStr("form_code"));
                ResultSet rs = pstmt.executeQuery();
                // 获取列数
                ResultSetMetaData rsMetaData = rs.getMetaData();
                int colCount = rsMetaData.getColumnCount();
                while (rs.next()) {
                    // 遍历每一列
                    System.out.println("名字"+rs.getObject("property_en_name")+"\t数据"+rs.getObject("detailed_data"));
                    String colname = rs.getString(1);
                    jsonObj.set(colname,rs.getObject("detailed_data"));
                    System.out.println("子查询");
                }
                //关闭资源
                rs.close();
                pstmt.close();
                con.close();

                System.out.println("查询");

                jsonObj.set("event_time",resultSet.getString("event_time"));
                array.add(jsonObj);
            }
            JSONObject result = new JSONObject();
            result.set("data",array);

            resultSet.close();
            preparedStatement.close();
            result.set("code",0);
            return result;
        }catch(Exception ex){
            return null;
        }
        finally {
            try {
                if (resultSet != null) resultSet.close();
                if (preparedStatement != null) preparedStatement.close();
                conn.close();
            } catch (SQLException e) {
                System.out.println(e.getMessage());
            }
        }
    }
    //条件查询
    public static JSONObject partselect1(String event_code,String status,String reporter_dept,String date,String reporter) {
        Connection conn = DbUtil.getConn();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            if (conn == null && conn.isClosed()) {
                return null;
            }

            preparedStatement = conn.prepareStatement(
                    "SELECT * FROM event_all,event_kind,status_table,reporter where event_all.event_code = event_kind.kind_code and event_status = status_id and event_all.reporter_code=reporter.reporter_code and event_all.event_code=?"+
                            " and event_status like '%"+status+"%' and dept_name like '%"+reporter_dept+"%' and event_time like '%"+date+"%' and reporter_name  like '%"+reporter+"%' and event_all.event_status=2");
            preparedStatement.setString(1,event_code);
            resultSet = preparedStatement.executeQuery();
            // 获取列数
            ResultSetMetaData metaData = resultSet.getMetaData();
            int columnCount = metaData.getColumnCount();

            // 遍历ResultSet中的每条数据
            JSONArray array = new JSONArray();
            while (resultSet.next()) {
                JSONObject jsonObj = new JSONObject();

                // 遍历每一列
                for (int i = 1; i <= columnCount; i++) {

                    String columnName = metaData.getColumnLabel(i);
                    Object value = resultSet.getObject(columnName);
                    jsonObj.set(columnName,value);
                }

                //查询粗略信息
                Connection con = DbUtil.getConn();
                PreparedStatement pstmt = con.prepareStatement("select property_en_name,detailed_data from event_property,event_detailed where event_property.property_code = event_detailed.property_code and form_code = ?");
                pstmt.setString(1,jsonObj.getStr("form_code"));
                ResultSet rs = pstmt.executeQuery();
                // 获取列数
                ResultSetMetaData rsMetaData = rs.getMetaData();
                int colCount = rsMetaData.getColumnCount();
                while (rs.next()) {
                    // 遍历每一列
                    System.out.println("名字"+rs.getObject("property_en_name")+"\t数据"+rs.getObject("detailed_data"));
                    String colname = rs.getString(1);
                    jsonObj.set(colname,rs.getObject("detailed_data"));
                    System.out.println("子查询");
                }
                //关闭资源
                rs.close();
                pstmt.close();
                con.close();

                System.out.println("查询");

                jsonObj.set("event_time",resultSet.getString("event_time"));
                array.add(jsonObj);
            }
            JSONObject result = new JSONObject();
            result.set("data",array);

            resultSet.close();
            preparedStatement.close();
            result.set("code",0);
            return result;
        }
        catch (SQLException ex){

            return null;
        }
        finally {
            try {
                if (resultSet != null) resultSet.close();
                if (preparedStatement != null) preparedStatement.close();
                conn.close();
            } catch (SQLException e) {
                System.out.println(e.getMessage());
            }
        }
    }

}
